package db;

import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Properties;

import entity.DecisionLinesEvent;
import entity.Edge;
import entity.Line;
import entity.User;

/**
 * Interface to the MySQL database.
 * 
 * updates to meetings/participants/availability
 * 
 * @author heineman
 */
public class Manager {

	/** Hard-coded database access information */
	private static final String SERVER = "SERVER";
	private static final String USER = "USER";
	private static final String PASSWORD = "PASSWORD";
	private static final String DATABASE = "DATABASE";

	// as long as you're using mysql, leave this alone.
	private static final String DATABASE_TYPE = "mysql";

	/* ------------- SQL Variables ------------- */

	/** The SQL connection to the database */
	static Connection con;

	/**
	 * Gets a connection to for the manager and makes a good effort to make sure
	 * it is open
	 * 
	 * @return either an open connection or null
	 */
	static synchronized Connection getConnection() {
		try {
			if (con != null && con.isClosed()) {
				con = null;
			}
		} catch (SQLException e) {
			con = null;
		}
		connect();
		return con;
	}

	/** Closes the database connection */
	public static void disconnect() {
		if (con == null) {
			return;
		}

		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		con = null;
	}

	/**
	 * Utility method to validate connection is valid.
	 * 
	 * @return true if DATABASE is available; false otherwise.
	 */
	public static boolean isConnected() {
		if (con == null) {
			return false;
		}

		try {
			return !con.isClosed();
		} catch (SQLException e) {
			e.printStackTrace();

			return false;
		}
	}

	public static boolean connect() {
		// already connected.
		if (con != null) {
			return true;
		}

		// Register the JDBC driver for MySQL. Simply accessing the class
		// will properly initialize everything.
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println("Unable to locate mySQL drivers");
			return false;
		}

		Properties dbConfig = new Properties();
		try {
			dbConfig.load(new FileReader(new File("db.config")));
		} catch (Exception e1) {
			System.err
					.println("Unable to locate db.config configuration file.");
			return false;
		}

		// Define URL for database server
		// NOTE: must fill in DATABASE NAME
		String url = "jdbc:" + DATABASE_TYPE + "://"
				+ dbConfig.getProperty(SERVER) + "/"
				+ dbConfig.getProperty(DATABASE);

		try {
			// Get a connection to the database for a
			// user with the given user name and password.
			con = DriverManager.getConnection(url, dbConfig.getProperty(USER),
					dbConfig.getProperty(PASSWORD));
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	/** Insert meeting into database. */
	public static boolean insertEvent(String id, String question,
			String eventType, String turnType, int numChoices, int numEdges,
			String phase, ArrayList<User> users, ArrayList<Line> lines) {
		try {
			PreparedStatement pstmt = Manager
					.getConnection()
					.prepareStatement(
							"INSERT into events(id,eventType,turnType,numberOfChoice,numberOfEdge, remainingEdge, creationDate, phase,question,numUsers) "
									+ " VALUES(?,?,?,?,?,?,?,?,?,?);");
			pstmt.setString(1, id);
			pstmt.setString(2, eventType);
			pstmt.setString(3, turnType);
			pstmt.setInt(4, numChoices);
			pstmt.setInt(5, numEdges);
			pstmt.setInt(6, numEdges);
			pstmt.setTimestamp(7,
					new java.sql.Timestamp(new java.util.Date().getTime()));
			pstmt.setString(8, phase);
			pstmt.setString(9, question);
			pstmt.setInt(10, users.size());

			// Execute the SQL statement and update database accordingly.
			pstmt.executeUpdate();

			int numInserted = pstmt.getUpdateCount();
			if (numInserted == 0) {
				throw new IllegalArgumentException("Unable to insert model "
						+ id + ".");
			}

			if (lines != null && lines.size() > 0)
				insertLines(id, lines);
			if (users.size() > 0)
				insertUsers(id, users);
		} catch (SQLException e) {
			// This model is already in the database. Simply update its values.
			e.printStackTrace();
			// return updateEvent(id, event);
		}

		return true;
	}

	/**
	 * Inserts initial list of lines for a closed event
	 * 
	 * @param eventId
	 * @param lines
	 * @return True for success, false otherwise
	 */
	private static void insertLines(String eventId, ArrayList<Line> lines) {
		try {
			PreparedStatement query = Manager
					.getConnection()
					.prepareStatement(
							"INSERT into draw2choose.lines(event,nameOfChoice,position) VALUES(?,?,?);");

			query.setString(1, eventId);
			for (int i = 0; i < lines.size(); i++) {
				query.setString(2, lines.get(i).getNameOfChoice());
				query.setInt(3, lines.get(i).getPosition());
				query.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static void insertEdges(long lineId, ArrayList<Edge> edges) {
		try {
			PreparedStatement query = Manager
					.getConnection()
					.prepareStatement(
							"INSERT into edges(lineId,edges.left,edges.right,edges.height) VALUES(?,?,?,?);");

			query.setLong(1, lineId);
			for (Edge e : edges) {
				query.setInt(2, e.getLeft());
				query.setInt(3, e.getRight());
				query.setInt(4, e.getHeight());
				query.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Adds the given list of users to the database for the given event
	 * 
	 * @param eventId
	 * @param users
	 */
	private static void insertUsers(String eventId, ArrayList<User> users) {
		try {
			PreparedStatement query = Manager
					.getConnection()
					.prepareStatement(
							"INSERT into draw2choose.users(event,username,password) VALUES(?,?,?);");
			query.setString(1, eventId);
			for (User u : users) {
				query.setString(2, u.getUserName());
				query.setString(3, u.getPassword());
				query.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Updates the Lines for a given event.
	 * 
	 * @param eventId
	 * @param lines
	 */
	private static void updateLines(String eventId, ArrayList<Line> lines) {
		ArrayList<Line> newLines = new ArrayList<Line>();
		ArrayList<Line> oldLines = new ArrayList<Line>();

		// Find new lines in the array
		for (int i = 0; i < lines.size(); i++) {
			if (lines.get(i).getDbId() < 0) {
				newLines.add(lines.get(i));
			} else {
				oldLines.add(lines.get(i));
			}
		}

		// Insert the new lines
		insertLines(eventId, newLines);

		// Update all lines
		try {
			PreparedStatement query = Manager
					.getConnection()
					.prepareStatement(
							"UPDATE draw2choose.lines SET nameOfChoice=?,nameOfDecision=? WHERE id=?;");
			for (Line l : lines) {
				query.setString(1, l.getNameOfChoice());
				query.setString(2, l.getNameOfDecision());
				query.setLong(3, l.getDbId());
				query.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// Update the edges for all old lines (new lines can't possibly have
		// edges yet, right?)
		for (Line l : oldLines) {
			updateEdges(l.getDbId(), l.getEdges());
		}
	}

	/**
	 * Inserts new users for the given eventId. Currently doesn't update
	 * existing ones, since the username and password shouldn't change.
	 * 
	 * @param eventId
	 * @param users
	 */
	private static void updateUsers(String eventId, ArrayList<User> users) {

		ArrayList<User> existingUsers = retrieveUsers(eventId);
		ArrayList<User> removedUsers = new ArrayList<User>();
		ArrayList<User> newUsers = new ArrayList<User>();

		for (int i=0;i<users.size();i++) {
			boolean found = false;
			for (int j=0;j<existingUsers.size();j++) {
				if (users.get(i).getUserName().equals(existingUsers.get(j).getUserName())
						&& users.get(i).getPassword().equals(existingUsers.get(j).getPassword())) {
					found = true;
					break;
				}
			}
			if (!found) {
				newUsers.add(users.get(i));
			}
		}
		
		for (int i=0;i<existingUsers.size();i++) {
			boolean found = false;
			for (int j=0;j<users.size();j++) {
				if (users.get(i).getUserName().equals(existingUsers.get(j).getUserName())
						&& users.get(i).getPassword().equals(existingUsers.get(j).getPassword())) {
					found = true;
					break;
				}
			}
			if (!found) {
				removedUsers.add(existingUsers.get(i));
			}
		}
		
		// insert users remaining in users
		insertUsers(eventId, newUsers);
		// delete anyone that was kicked
		removeUsers(eventId, removedUsers);
	}



	/**
	 * Finds and inserts any new edges for the given lineId. Old edges are
	 * assumed to have not changed and are not updated.
	 * 
	 * @param lineId
	 * @param edges
	 */
	private static void updateEdges(long lineId, ArrayList<Edge> edges) {
		ArrayList<Edge> existingEdges = retrieveEdges(lineId);
		ArrayList<Edge> newEdges = new ArrayList<Edge>();

		// Find new edges, old ones will not change
		for (Edge e : edges) {
			boolean newEdge = true;
			for (Edge f : existingEdges) {
				if (e.getLeft() == f.getLeft() && e.getRight() == f.getRight()
						&& e.getHeight() == f.getHeight()) {
					newEdge = false;
				}
			}
			if (newEdge)
				newEdges.add(e);
		}
		insertEdges(lineId, newEdges);
	}

	/**
	 * Updates the event in the database, including users, lines, and edges.
	 * 
	 * @param id
	 * @param event
	 * @return
	 */
	public static boolean updateEvent(String id, DecisionLinesEvent event) {

		try {
			PreparedStatement pstmt = Manager
					.getConnection()
					.prepareStatement(
							"UPDATE events set eventType=?,turnType=?,numberOfChoice=?,numberOfEdge=?,remainingEdge=?,turn=?,creationDate=?,phase=?,question=?,numUsers=? where id=?;");

			pstmt.setString(1, event.getEventType());
			pstmt.setString(2, event.getTurnType());
			pstmt.setInt(3, event.getNumChoices());
			pstmt.setInt(4, event.getNumRounds());
			pstmt.setInt(5, event.getRemainingEdge());
			pstmt.setString(6, event.getTurn());
			pstmt.setTimestamp(7, event.getCreationDate());
			pstmt.setString(8, event.getPhase());
			pstmt.setString(9, event.getQuestion());
			pstmt.setInt(10, event.getNumUser());
			pstmt.setString(11, id);

			// Execute the SQL statement and update database accordingly.
			pstmt.executeUpdate();

			updateLines(id, event.getLines());
			updateUsers(id, event.getUsers());

			int numUpdated = pstmt.getUpdateCount();
			if (numUpdated == 0) {
				throw new IllegalArgumentException("Unable to update event "
						+ id + ".");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("unable to update!");
			return false;
		}

		return true;
	}

	/**
	 * Retrieves the event from the DB associated with the given event ID
	 * 
	 * @param id
	 * @return
	 */
	public static DecisionLinesEvent retrieveEvent(String id) {
		try {
			PreparedStatement pstmt = Manager.getConnection().prepareStatement(
					"SELECT * FROM events where id=?;");
			pstmt.setString(1, id);
			ResultSet result = pstmt.executeQuery();

			if (!result.next()) {
				return null;
			}

			DecisionLinesEvent e = new DecisionLinesEvent(
					id,
					result.getString("eventType"),
					result.getString("turnType"), result.getString("question"),
					result.getInt("numberOfEdge"),
					result.getInt("numberOfChoice"), result.getInt("numUsers"));
			e.setRemainingEdge(result.getInt("remainingEdge"));
			e.setTurn(result.getString("turn"));
			e.setCreationDate(result.getTimestamp("creationDate"));
			e.setPhase(result.getString("phase"));

			ArrayList<Line> lines = new ArrayList<Line>();
			for (Line l : retrieveLines(id)) {
				lines.add(l);
			}
			e.setLines(lines);
			e.setUsers(retrieveUsers(id));

			return e;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * Returns a list of all events in the database of the given type.
	 * 
	 * @param type The desired type ('open' or 'closed')
	 * @return List of events
	 */
	public static List<DecisionLinesEvent> retrieveEventsByType(String type) {
		
		try {
			
			// Get all events from the database of the given type
			PreparedStatement pstmt = Manager.getConnection().prepareStatement(
					"SELECT * FROM events where eventType=?");
			pstmt.setString(1, type);
			ResultSet result = pstmt.executeQuery();
			
			List<DecisionLinesEvent> events = new ArrayList<DecisionLinesEvent>();
			
			// Loop through all events in the result set, creating a DecisionLinesEvent object for
			// each of them and adding them to the list of events.
			while (result.next()) {
				String id = result.getString("id");
				DecisionLinesEvent e = new DecisionLinesEvent(
						id,
						result.getString("eventType"),
						result.getString("turnType"), result.getString("question"),
						result.getInt("numberOfEdge"),
						result.getInt("numberOfChoice"), result.getInt("numUsers"));
				e.setRemainingEdge(result.getInt("remainingEdge"));
				e.setTurn(result.getString("turn"));
				e.setCreationDate(result.getTimestamp("creationDate"));
				e.setPhase(result.getString("phase"));

				ArrayList<Line> lines = new ArrayList<Line>();
				for (Line l : retrieveLines(id)) {
					lines.add(l);
				}
				e.setLines(lines);
				e.setUsers(retrieveUsers(id));
				
				events.add(e);
			}
			
			// Return the constructed list of events
			return events;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		// If an error occurs, simply return an empty list
		return Collections.emptyList();
	}

	/**
	 * Retrieves the lines associated with a given event, including retrieving
	 * all associated edges.
	 * 
	 * @param eventId
	 * @return
	 */
	private static ArrayList<Line> retrieveLines(String eventId) {

		try {
			PreparedStatement pstmt = Manager.getConnection().prepareStatement(
					"SELECT * FROM draw2choose.lines where event=?;");
			pstmt.setString(1, eventId);
			ResultSet result = pstmt.executeQuery();

			ArrayList<Line> lines = new ArrayList<Line>();
			while (result.next()) {
				Line l = new Line(result.getString("nameOfChoice"),
						result.getInt("position"));
				l.setNameOfDecision(result.getString("nameOfDecision"));
				l.setDbId(result.getInt("id"));
				l.setEdges(retrieveEdges(l.getDbId()));
				lines.add(l);
			}

			// Sort the lines to maintain correct order
			Collections.sort(lines, new lineComparator());

			return lines;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	private static class lineComparator implements Comparator<Line> {
		@Override
		public int compare(Line arg0, Line arg1) {
			return (arg0.getPosition() > arg1.getPosition() ? 1 : -1);
		}
	}

	/**
	 * Retrieves the edges associated with a given line.
	 * 
	 * @param lineId
	 * @return
	 */
	private static ArrayList<Edge> retrieveEdges(long lineId) {
		try {
			PreparedStatement pstmt = Manager.getConnection().prepareStatement(
					"SELECT * FROM edges where lineId=?;");
			pstmt.setLong(1, lineId);
			ResultSet result = pstmt.executeQuery();

			ArrayList<Edge> edges = new ArrayList<Edge>();
			while (result.next()) {
				int left = result.getInt("left");
				int right = result.getInt("right");
				int height = result.getInt("height");
				edges.add(new Edge(left, height, right));
			}
			return edges;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Retrieves the users associated with a given event.
	 * 
	 * @param eventId
	 * @return
	 */
	private static ArrayList<User> retrieveUsers(String eventId) {
		try {
			PreparedStatement pstmt = Manager
					.getConnection()
					.prepareStatement(
							"SELECT * FROM draw2choose.users where event=? ORDER BY id ASC;");
			pstmt.setString(1, eventId);
			ResultSet result = pstmt.executeQuery();
			ArrayList<User> users = new ArrayList<User>();

			while (result.next()) {
				String username = result.getString("username");
				String password = result.getString("password");
				users.add(new User(username, password));
			}
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Deletes an event and all associated users, lines, and edges from the
	 * database.
	 * 
	 * @param eventId
	 */
	public static boolean deleteEvent(String eventId) {
		try {
			DecisionLinesEvent e = retrieveEvent(eventId);
			// If the event doesn't exist, don't bother trying to delete
			// anything
			if (e == null)
				return false;

			// Delete users from event
			PreparedStatement query = Manager.getConnection().prepareStatement(
					"DELETE from users WHERE event = ?;");
			query.setString(1, eventId);
			query.executeUpdate();

			// Delete edges for each line, then each line
			query = Manager.getConnection().prepareStatement(
					"DELETE from edges WHERE lineId = ?;");
			for (Line line : e.getLines()) {
				query.setLong(1, line.getDbId());
				query.executeUpdate();
			}

			// Delete the lines themselves
			query = Manager.getConnection().prepareStatement(
					"DELETE from draw2choose.lines WHERE event = ?;");
			query.setString(1, eventId);
			query.executeUpdate();

			// Delete the event
			query = Manager.getConnection().prepareStatement(
					"DELETE from events WHERE id = ?;");
			query.setString(1, eventId);
			query.executeUpdate();

		} catch (SQLException e) {
			throw new IllegalArgumentException(e.getMessage(), e);
		}

		return true;
	}
	
	private static void removeUsers(String eventId, ArrayList<User> users) {
		PreparedStatement query;
		try {
			for (User u : users) {
				query = Manager.getConnection().prepareStatement(
						"DELETE from users WHERE event = ? AND username = ?;");
				query.setString(1, eventId);
				query.setString(2, u.getUserName());
				query.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * Deletes all events matching the given criteria.
	 * 
	 * @param completed
	 * @param olderThan
	 * @return
	 */
	public static int deleteEventByCriteria(boolean completed,
			java.util.Date olderThan) {
		int deletedCount = 0;
		try {
			String queryStr = "SELECT * from events WHERE creationDate < ? AND phase ";
			if (completed) {
				queryStr += "= 'completed';";
			} else {
				queryStr += "<> 'completed';";
			}
			PreparedStatement query = Manager.getConnection().prepareStatement(
					queryStr);
			query.setTimestamp(1, new Timestamp(olderThan.getTime()));

			ResultSet result = query.executeQuery();
			while (result.next()) {
				String eventId = result.getString("id");
				deleteEvent(eventId);
				deletedCount++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return deletedCount;
	}

	/** Make sure string is no longer than the given length. */
	public static String trimString(String s, int len) {
		if (s.length() <= len) {
			return s;
		}

		return s.substring(0, len);
	}
}
